* Table 13
* Doctorates only: share by estab and regpres 
local table "Table13"
use "./data/academics.dta", clear
createvarsacad
qui putexcel set "./tables/tables.xlsx", sheet("`table'") modify
qui putexcel D1=("2001")
qui putexcel H1=("2018")
qui putexcel A2=("Institution")
qui putexcel B2=("N")
qui putexcel C2=("% Exc")
qui putexcel D2=("% Int")
qui putexcel E2=("% Par")
qui putexcel F2=("N")
qui putexcel G2=("% Exc")
qui putexcel H2=("% Int")
qui putexcel I2=("% Par")
decode mainaff,gen(estab)
splitstr dum estab
local pos=3
foreach name in ///
"U LISBOA" "U NOVA" "U PORTO" "U MINHO" "U COIMBRA" /// 
"ISCTE IUL" "U CATÓLICA PORTUGUESA" "U ÉVORA" "U BEIRA INTERIOR" "U AVEIRO" "U ALGARVE" ///
"U AÇORES" "U MADEIRA" "UTAD" {
qui putexcel A`pos'=("`name'")
qui count if estab=="`name'" & year==2001 & doctor==1
local n2001=r(N)
qui count if estab=="`name'" & year==2001 & ///
regpres==1  & doctor==1 /* DEDICACAO EXCLUSIVA */
local sh1_2001=r(N)/`n2001'
qui count if estab=="`name'" & year==2001 & ///
regpres==2 & doctor==1 /* TEMPO INTEGRAL */
local sh2_2001=r(N)/`n2001'
qui count if estab=="`name'" & year==2001 & ///
regpres==3 & doctor==1 /* TEMPO PARCIAL */
local sh3_2001=r(N)/`n2001'
qui count if estab=="`name'" & year==2018 & doctor==1
local n2018=r(N)
qui count if estab=="`name'" & year==2018 & ///
regpres==1  & doctor==1 /* DEDICACAO EXCLUSIVA */
local sh1_2018=r(N)/`n2018'
qui count if estab=="`name'" & year==2018 & ///
regpres==2  & doctor==1 /* TEMPO INTEGRAL */
local sh2_2018=r(N)/`n2018'
qui count if estab=="`name'" & year==2018 & ///
regpres==3  & doctor==1 /* TEMPO PARCIAL */
local sh3_2018=r(N)/`n2018'
qui putexcel B`pos'=(`n2001'), nformat(#)
qui putexcel C`pos'=(`sh1_2001'), nformat(#.0%)
qui putexcel D`pos'=(`sh2_2001'), nformat(#.0%)
qui putexcel E`pos'=(`sh3_2001'), nformat(#.0%)
qui putexcel F`pos'=(`n2018'), nformat(#)
qui putexcel G`pos'=(`sh1_2018'), nformat(#.0%)
qui putexcel H`pos'=(`sh2_2018'), nformat(#.0%)
qui putexcel I`pos'=(`sh3_2018'), nformat(#.0%)
local ++pos
}
qui putexcel A`pos'=("All")
qui count if year==2001 & doctor==1
local n2001=r(N)
qui count if year==2001 & ///
regpres==1  & doctor==1 /* DEDICACAO EXCLUSIVA */
local sh1_2001=r(N)/`n2001'
qui count if year==2001 & ///
regpres==2  & doctor==1 /* TEMPO INTEGRAL */
local sh2_2001=r(N)/`n2001'
qui count if year==2001 & ///
regpres==3  & doctor==1 /* TEMPO PARCIAL */
local sh3_2001=r(N)/`n2001'
qui count if year==2018 & doctor==1
local n2018=r(N)
qui count if year==2018 & ///
regpres==1  & doctor==1 /* DEDICACAO EXCLUSIVA */
local sh1_2018=r(N)/`n2018'
qui count if year==2018 & ///
regpres==2  & doctor==1 /* TEMPO INTEGRAL */
local sh2_2018=r(N)/`n2018'
qui count if year==2018 & ///
regpres==3  & doctor==1 /* TEMPO PARCIAL */
local sh3_2018=r(N)/`n2018'
qui putexcel B`pos'=(`n2001'), nformat(#)
qui putexcel C`pos'=(`sh1_2001'), nformat(#.0%)
qui putexcel D`pos'=(`sh2_2001'), nformat(#.0%)
qui putexcel E`pos'=(`sh3_2001'), nformat(#.0%)
qui putexcel F`pos'=(`n2018'), nformat(#)
qui putexcel G`pos'=(`sh1_2018'), nformat(#.0%)
qui putexcel H`pos'=(`sh2_2018'), nformat(#.0%)
qui putexcel I`pos'=(`sh3_2018'), nformat(#.0%)
